package com.winning.sjzc.sjypz.controller;

import com.winning.sjzc.common.utils.*;
import com.winning.sjzc.sjypz.entity.SjzcSjjywxxEntity;
import com.winning.sjzc.sjypz.entity.SjzcSjkxxEntiy;
import com.winning.sjzc.sjypz.service.SjzcSjjywxxService;
import com.winning.sjzc.sjypz.service.SjzcSjkxxService;
import com.winning.sjzc.sjypz.utils.SqlExecutorUtils;
import com.winning.sjzc.zyml.entity.PsmsSjjxxEntity;
import com.winning.sjzc.zyml.service.PsmsSjjxxService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Date;
import java.util.List;
import java.util.Map;

import static com.winning.sjzc.common.controller.BaseController.baseError;
import static com.winning.sjzc.common.controller.BaseController.successObjectResult;

/**
 * description: 数据源配置
 *
 * @author yanxf@winning.com.cn
 * @date 2019/08/14 14:27
 */
@Slf4j
@RequestMapping(value="/sjypz")
@Controller
public class SjypzContrller {

    @Autowired
    private SjzcSjkxxService sjkxxService;

    @Autowired
    private PsmsSjjxxService psmsSjjxxService;

    @Autowired
    private SjzcSjjywxxService sjzcSjjywxxService;

    @Value("${oracleSync}")
    public String oracleSync;

    /**
     *@描述 测试数据源连接
     *@参数
     *@返回值
     *@创建人  wujiaxing
     *@创建时间  2019/8/15
     */
    @RequestMapping(value="/testDbConnection",method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> testDbConnection(@RequestBody SjzcSjkxxEntiy sjzcSjkxxEntiy){
        boolean ifConnect = false;
        try {
            ifConnect = sjkxxService.testDbConnect(sjzcSjkxxEntiy);
        } catch (Exception e) {
            log.error("数据库测试连接失败",e);
            return baseError(e.toString(),500);
        }
        if(ifConnect) {
            return successObjectResult("连接成功", 1);
        }else{
            return baseError("连接失败",500);
        }
    }

    /**
     *@描述 
     *@参数  保存数据源信息
     *@返回值  
     *@创建人  wujiaxing
     *@创建时间  2019/8/15
     */
    @RequestMapping(value="/save",method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> saveDbInfo(@RequestBody SjzcSjkxxEntiy sjzcSjkxxEntiy){
        SjzcSjkxxEntiy sjkxx = null;
        int result = 0;
        try {
            sjkxx = sjkxxService.getSjkxxBySjkdm(sjzcSjkxxEntiy.getSjkdm());
            if(null != sjkxx){
                //更新数据库信息
                result = sjkxxService.updateSjkxx(sjzcSjkxxEntiy);
            }else{
                //新增数据库信息
                result = sjkxxService.save(sjzcSjkxxEntiy);
            }
        }catch (Exception e){
            log.error("数据源保存失败",e);
            return baseError("保存失败",500);
        }
        if(result>0){
            return successObjectResult("保存成功", "");
        }else{
            return baseError("保存失败",500);
        }

    }

    /**
     *@描述 
     *@参数  同步数据库、表存储详情
     *@返回值  
     *@创建人  wujiaxing
     *@创建时间  2019/8/15
     */
    @RequestMapping(value="/synchronizationDbInfo",method = RequestMethod.GET)
    @ResponseBody
    public Map<String, Object> synchronizationDbInfo(String sjkdm){
        SjzcSjkxxEntiy sjkxxEntiy = null;
        Connection con = null;
        String brlSql = "";
        String count = "";
        String brl = "";
        ResultSet rsBrl = null;
        try {
            sjkxxEntiy = sjkxxService.getSjkxxBySjkdm(sjkdm);
            if(null == sjkxxEntiy){
                return baseError("同步失败,未查询到需要同步的数据库信息",500);
            }
            con = sjkxxService.getDruidConnection(sjkxxEntiy);
            List<PsmsSjjxxEntity> sjjList = psmsSjjxxService.findAll();
            for (PsmsSjjxxEntity psmsSjjxxEntity : sjjList) {
                String sjjdm = psmsSjjxxEntity.getSjjdm();
                String sjjmc = psmsSjjxxEntity.getSjjmc();
                log.info("同步表："+sjjdm+" 信息开始，执行存储："+brlSql);
                String[] params = {sjjdm};
                if(StringUtils.equalsIgnoreCase(sjkxxEntiy.getSjklx(),"SQLSERVER")){
                    brlSql = "{call sp_Sjzc_synchronization_tableSize(?) }";
                    rsBrl = SqlExecutorUtils.excutePro(con,brlSql,params,sjkxxEntiy.getSjklx());
                }else if(StringUtils.equalsIgnoreCase(sjkxxEntiy.getSjklx(),"ORACLE")){
                    brlSql = oracleSync;
                    rsBrl = SqlExecutorUtils.excuteQuery(con,brlSql,params);
                }
                if(null == rsBrl){
                    continue;
                }
                while(rsBrl.next()){
                    count = rsBrl.getString("表数据行数");
                    brl = rsBrl.getString("数据大小");
                }
                Date gxsj = DateUtil.getDate();
                SjzcSjjywxxEntity sjjywxxEntity = sjzcSjjywxxService.getBySjjdm(sjjdm);
                if(null != sjjywxxEntity){
                    SjzcSjjywxxEntity updateSjjywxx = new SjzcSjjywxxEntity(
                            CommonUtils.getDataSize(count),
                            CommonUtils.getDataSize(brl),gxsj);
                    sjzcSjjywxxService.update(updateSjjywxx,sjjdm);
                }else{
                    sjjywxxEntity = new SjzcSjjywxxEntity(sjjdm,sjjmc,
                            CommonUtils.getDataSize(count),
                            CommonUtils.getDataSize(brl),gxsj,gxsj);
                    sjzcSjjywxxService.insert(sjjywxxEntity);
                }
                log.info("同步表："+sjjdm+" 信息成功完成");
            }
        } catch (Exception e) {
            log.error("同步数据库、表存储详情失败",e);
            return baseError("同步数据库、表存储详情失败",500);
        }
        return successObjectResult("同步数据库信息成功", "");
    }

    /**
     *@描述 更新数据库信息
     *@参数
     *@返回值
     *@创建人  wujiaxing
     *@创建时间  2019/8/19
     */
    @RequestMapping(value="/updateDbInfo",method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> updateDbInfo(@RequestBody SjzcSjkxxEntiy sjzcSjkxxEntiy){

        return successObjectResult("修改数据库信息成功", "");
    }

    /**
     *@描述 跳转页面
     *@参数
     *@返回值
     *@创建人  wujiaxing
     *@创建时间  2019/8/19
     */
    @RequestMapping("/list")
    public ModelAndView toSjypz(Model model) {
        List<SjzcSjkxxEntiy> list = sjkxxService.findAll();
        model.addAttribute("sjkxxlist",list);
        ModelAndView mv = new ModelAndView("sjypz");
        return mv;
    }

    /**
     *@描述
     *@参数
     *@返回值
     *@创建人  wujiaxing
     *@创建时间  2019/8/20
     */
    @RequestMapping("/add")
    public ModelAndView toSjyAdd(Model model){
        ModelAndView mv = new ModelAndView("sjkxx-add");
        return mv;
    }

    /**
     *@描述 
     *@参数
     *@返回值  
     *@创建人  wujiaxing
     *@创建时间  2019/8/19
     */
    @RequestMapping(value = "/detail",method = RequestMethod.GET)
    @ResponseBody
    public Map<String, Object> detail(String sjkdm) {
        SjzcSjkxxEntiy sjkxxEntiy = null;
//        ModelAndView mv = new ModelAndView("sjypz::editSjypz");
        try{
            sjkxxEntiy = sjkxxService.getSjkxxBySjkdm(sjkdm);
        }catch (Exception e){
            log.error("查询数据库详情失败",e);
            return baseError("失败",500);
        }
        return successObjectResult("查询信息成功", sjkxxEntiy);
    }

    /**
     *@描述
     *@参数
     *@返回值
     *@创建人  wujiaxing
     *@创建时间  2019/8/21
     */
    @RequestMapping(value = "/del",method = RequestMethod.GET)
    @ResponseBody
    public Map<String, Object> del(String sjkdm) {
        int deletere = 0;
        if(StringUtils.isBlank(sjkdm)){
            return successObjectResult("删除成功", "");
        }
        try{
            deletere = sjkxxService.delBySjkdm(sjkdm);
        }catch (Exception e){
            log.error("删除数据库失败",e);
            return baseError("删除数据库失败",500);
        }
        if(deletere > 0){
            return successObjectResult("删除成功", "");
        }else{
            return baseError("删除失败",500);
        }
    }
}
